package com.example.tms.mapper;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.example.tms.bean.CarInfo;
import com.example.tms.bean.DriverInfo;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;
import java.util.Map;

/**
 * 司机信息
 */
@Repository
public interface DriverMapper extends JpaRepository<DriverInfo, String>, JpaSpecificationExecutor<DriverInfo>{

    //查询司机信息（司机id）
    @Query(value = "select * from driver_info where sjid = ?1 and sfcz = true ", nativeQuery = true)
    DriverInfo findBySjid(String sjid);

    @Query(value = "select * from driver_info where sjid = ?1 ", nativeQuery = true)
    DriverInfo findBySjidnotTrue(String sjid);

    //查询司机信息（司机id）
    @Query(value = "select * from driver_info where sfzh = ?1 ", nativeQuery = true)
    DriverInfo findBySfzh(String sfzh);

    //查询司机信息（部门id）
    @Query(value = "select * from driver_info where bumenid = ?1 and sfcz = true", nativeQuery = true)
    List<DriverInfo> findByBumenid(String bumenid);

    //删除司机信息（假删除 司机id）
    @Query(value = "update driver_info set sfcz = false where sjid = ?1", nativeQuery = true)
    @Modifying
    @Transactional
    void deleteBySjid(String sjid);

    List<DriverInfo> findBySfczOrderByLasttimeAsc(Boolean sfcz);

    //查询司机信息（驾驶员手机）
    List<DriverInfo> findByJsysjAndSfcz(String jsysjh, boolean sfcz);

    //查询司机信息（身份证号）
    DriverInfo findBySfzhAndSfcz(String sfzh, boolean sfcz);

    //查询司机信息（司机姓名）
    DriverInfo findByJsyxm(String jsyxm);

    //车辆添加驾驶员（车辆id）
    @Query(value = "update driver_info set clid = ?1 where sjid = ?2", nativeQuery = true)
    @Modifying
    @Transactional
    void updateDriverCar(String clid, String sjid);

    //查询司机姓名
    @Query(value = "select  DISTINCT jsyxm,lasttime  from driver_info where sfcz = true order by lasttime desc", nativeQuery = true)
    List<Object[]> findDriverNameList();

    //查询司机数量
    @Query(value = "select  count(DISTINCT jsysj)  from waybill_mgmt where ydzt!='已完成' and sfcz = true", nativeQuery = true)
    Integer findDriverOntheWay();

    //查询司机数量
    @Query(value = "select  count(sjid)  from driver_info where zzzt='在职' and sfcz = true", nativeQuery = true)
    Integer findDriverOnWork();

    //查询司机数量
    @Query(value = "select  count(sjid)  from driver_info where zzzt!='在职' and sfcz = true", nativeQuery = true)
    Integer findDriverOutWork();
}
